# -*- coding: utf-8 -*-
# @Time    : 2022/11/16 15:38
# @Author  : yangyuexiong
# @Email   : yang6333yyx@126.com
# @File    : main.py
# @Software: PyCharm

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

from commands.app_commands.Excel.excel_base.main import ExcelHandle, sort_range_tuple


class ExcelSortHandle(ExcelHandle):
    """排序"""

    def __init__(self, excel_path=None):
        super(ExcelSortHandle, self).__init__(excel_path=excel_path)

        self.sort_range_dict = {
            "custom_range": self.sort_only,
            "only": self.sort_only
        }

    def sort_only(self, sheet, **kwargs):
        """
        当前列排序
        :param sheet: sheet对象
        :param kwargs:
        :return:
        """

        print('=== sort_only ===')
        io = self.excel_path
        sheet_name = sheet.title
        column = kwargs.get('column')
        title = kwargs.get('title')
        sort_by = kwargs.get('sort_by')
        is_save_as = kwargs.get('is_save_as', False)
        new_path = kwargs.get('new_path')

        # print(title)
        # print(sort_by)
        df = pd.read_excel(io=io, sheet_name=sheet_name)
        df.sort_values(by=title, inplace=True, ascending=sort_by)  # True升序,False降序
        # print(df)

        # index:是否需要序号,header:是否标题覆盖
        sheet_columns = list(sheet.rows)
        data_result = list(dataframe_to_rows(df, index=False, header=True))

        for col_index, col in enumerate(sheet_columns):
            for cell_index, cell in enumerate(col):
                try:
                    cell.value = data_result[col_index][cell_index]
                except:
                    cell.value = ""

        """
        无效的Api
        sheet.auto_filter.add_sort_condition("A1:A6", True)
        sheet.auto_filter.add_filter_column(1, ['1', '4'])
        """

        self.excel_save_as(is_save_as=is_save_as, new_path=new_path)

    def excel_sort(self, sheet_name=None, sort_range=None, **kwargs):
        """
        排序
        :param sheet_name: sheet名称
        :param sort_range: 范围类型
        :param kwargs: 对应范围类型的扩展参数
            sort_by(排序方式):
                asc(升序)
                desc(降序)
            include_title(包含标题):
                True
                False
        :return:

        """
        try:
            if isinstance(self.wb, Workbook):
                self.wb.save(self.excel_path)
                # print(f"读取:{self.excel_path.split('/')[-1]}")

                if sort_range not in sort_range_tuple:
                    msg = f'排序范围错误:{sort_range}'
                    # print(msg)
                    self.throw['status'] = False
                    self.throw['message'] = msg
                    return self.throw

                if not self.check_sheet_name(sheet_name):
                    return self.throw

                sheet = self.get_sheet(sheet_name)
                kwargs['sheet_name'] = sheet_name
                kwargs['sheet'] = sheet
                sort_func = self.sort_range_dict.get(sort_range)
                sort_func(**kwargs)
                return self.throw

        except BaseException as e:
            # print(e)
            self.return_error_file()
            return self.throw
